package com.javasm.entity.sql;

/**
 * @author WangJie
 * @version 1.0
 * @Description:
 * @date: 2020/7/21 21:25
 * @since JDK 1.8
 */
public interface BookManageSql {

    // 分页查询图书信息
    String SELECT_BOOK_BY_PAGE = "SELECT\n" +
            "\tsb.bid,\n" +
            "\tsb.book_name,\n" +
            "\tsb.author_name,\n" +
            "\tsb.coverUrl,\n" +
            "\tsb.description,\n" +
            "\tsb.word_count,\n" +
            "\tsb.`status`,\n" +
            "\tsb.attribution,\n" +
            "\tsb.tsb_id,\n" +
            "\tsb.ssb_id,\n" +
            "\tstsb.tsb_name,\n" +
            "\tsssb.ssb_name \n" +
            "FROM\n" +
            "\tsystem_book sb,\n" +
            "\tsystem_top_sort_book stsb,\n" +
            "\tsystem_second_sort_book sssb \n" +
            "WHERE\n" +
            "\tsb.tsb_id = stsb.tsb_id \n" +
            "\tAND sb.ssb_id = sssb.ssb_id ";

    // 查询图书信息总记录数
    String SELECT_BOOK_TOTALCOUNT = "SELECT COUNT(1) FROM system_book sb ";

    // 查询图书一级分类
    String SELECE_BOOK_TOP_SORT = "SELECT tsb_id,tsb_name FROM system_top_sort_book";

    // 根据一级分类id查询图书二级分类
    String SELECE_BOOK_SECOND_SORT_BY_TSBID = "SELECT tsb_id,ssb_id,ssb_name FROM system_second_sort_book WHERE tsb_id = ?";

    // 分页查询图书分类信息
    String SELECT_BOOK_SORT_BY_PAGE = "SELECT\n" +
            "\tstsb.tsb_id,\n" +
            "\tstsb.tsb_name,\n" +
            "\tsssb.ssb_id,\n" +
            "\tsssb.ssb_name \n" +
            "FROM\n" +
            "\tsystem_top_sort_book stsb,\n" +
            "\tsystem_second_sort_book sssb \n" +
            "WHERE\n" +
            "\tstsb.tsb_id = sssb.tsb_id \n" +
            "\tLIMIT ?,?";

    // 查询图书分类信息的总记录数
    String SELECT_BOOK_SORT_TOTALCOUNT = "SELECT COUNT(1) FROM system_second_sort_book";

    // 新增图书二级分类
    String INSERT_BOOK_SECOND_SORT = "INSERT INTO system_second_sort_book(tsb_id,ssb_name) VALUES(?,?)";

    // 新增图书一级分类
    String INSERT_BOOK_TOP_SORT = "INSERT INTO system_top_sort_book(tsb_name) VALUES(?)";
}
